OpenAccess ORM for Oracle 實戰演練 Day 09

oracle vb linq orm openaccess

smartleos 2013-10-01 01:14:50 ‧ 1968 瀏覽

分享至

OpenAccess快速入門05
OpenAccess 快速入門將協助我們熟悉 OpenAccess，第五篇來討論常見的關聯搜尋技巧。
我們延續先前在快速入門一開始所建立的範例專案，以 Emoployees 為主，看一下它實體關聯結構：

這裡先看 Employees 和 Orders 就好，它們是 0..1 對多的關係，這表示一筆員工資料會對應多筆訂單，所以：
● Employees 身上有一個導覽屬性（Navigation）叫【Orders】，回傳的是一個 IList<Orders>。
● Orders 身上也有一個導覽屬性，叫【Employees】，回傳的則是一個 Employees 物件。

接下來我們可以透過這個導覽屬性取得所需的關聯資料！我們就繼續在 VerifyRLinq.vb 中加入新的方法，透過導覽屬性取得員工所關聯的訂單資料吧：

Public Class VerifyRLinq
    Private cxt As SecondModel

    Public Sub New()
        cxt = New SecondModel()
    End Sub

    Public Function GetThreeEmployeeOrders() As IDictionary(Of String, List(Of OrderSummary))
        Dim empList = (From x In cxt.Employees Select x).Take(3)
        Dim result As New Dictionary(Of String, List(Of OrderSummary))
        For Each employee In empList
            Dim empOrderList As New List(Of OrderSummary)
            Dim employeeName = GetFullName(employee.FIRSTNAME, employee.LASTNAME)
            Dim employeeOrders = employee.Orders.Take(5)
            For Each order In employeeOrders
                empOrderList.Add(GetEmployeeOrder(employee, order))
            Next
            result.Add(employeeName, empOrderList)
        Next

        Return result
    End Function

    Private Shared Function GetFullName(ByVal firstName As String, ByVal lastName As String) As String
        Dim employeeName = firstName + " " + lastName
        Return employeeName
    End Function

    Private Shared Function GetEmployeeOrder(ByVal employee As Employees, ByVal order As Orders) As OrderSummary
        Dim empOrder = New OrderSummary()
        empOrder.OrderId = order.ORDERID
        empOrder.CustomerId = order.CUSTOMERID
        empOrder.EmployeeId = order.EMPLOYEEID
        empOrder.EmployeeName = GetFullName(employee.FIRSTNAME, employee.LASTNAME)
        Return empOrder
    End Function
End Class

Public Class OrderSummary
    Public Property OrderId As String
    Public Property CustomerId As String
    Public Property EmployeeId As String
    Public Property EmployeeName As String
End Class

GetThreeEmployeeOrders 函式為核心，GetFullName 和 GetEmlplyeeOrder 只是重構拉出來不想分散注意力的小函式，所以請聚焦在 GetThreeEmployeeOrders 函式。

此函式中，我們先取出三筆員工資料，然後在 For Each 列舉時，透過導覽屬性 Orders 取得五筆訂單資料，然後以員工全名為 Key 值，搭配五筆訂單資料寫入一個 Dictionary，然後回傳給呼叫者。

非常簡單就完成了關聯式查詢，很棒吧！

為了方便大家查看執行結果，所以我們在 OpenAccessWebApp01 專案中，加入一個新的頁面叫 DisplayEmployeeOrders.aspx，放一個 Repeater 用來呈現資料，先看結果再貼程式碼給大家：

DisplayEmployeeOrders.aspx.vb

Imports OpenAccessWebApp01Model

Public Class DisplayEmployeeOrders
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim obj As New VerifyRLinq()
        Dim result = obj.GetThreeEmployeeOrders()
        Repeater1.DataSource = result
        Repeater1.DataBind()
    End Sub

End Class

DisplayEmployeeOrders.aspx

&lt;%@ Page Language="vb" AutoEventWireup="false" CodeBehind="DisplayEmployeeOrderSummary.aspx.vb" Inherits="OpenAccessWebApp01.DisplayEmployeeOrders" %>



&lt;html xmlns="http://www.w3.org/1999/xhtml">
&lt;head runat="server">
    &lt;meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    &lt;title>&lt;/title>


    &lt;form id="form1" runat="server">
        &lt;div>
            &lt;asp:Repeater ID="Repeater1" runat="server">
                &lt;ItemTemplate>
                    &lt;h2>
                        員工姓名：&lt;asp:Label ID="Label1" runat="server" Text='&lt;%# Eval("key")%>'>&lt;/asp:Label>&lt;/h2>
                    &lt;asp:GridView ID="GridView1" runat="server" DataSource='&lt;%# Eval("value")%>'>&lt;/asp:GridView>
                &lt;/ItemTemplate>
            &lt;/asp:Repeater>
        &lt;/div>
    &lt;/form>